{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>时间</th>\n",
       "      <th>击败</th>\n",
       "      <th>伤害</th>\n",
       "      <th>生存时间</th>\n",
       "      <th>治疗量</th>\n",
       "      <th>救援</th>\n",
       "      <th>积分</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>43338</td>\n",
       "      <td>15:48:00</td>\n",
       "      <td>2</td>\n",
       "      <td>200</td>\n",
       "      <td>11.6</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>43338</td>\n",
       "      <td>16:02:00</td>\n",
       "      <td>2</td>\n",
       "      <td>245</td>\n",
       "      <td>12.2</td>\n",
       "      <td>163</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>43338</td>\n",
       "      <td>16:18:00</td>\n",
       "      <td>1</td>\n",
       "      <td>100</td>\n",
       "      <td>18.8</td>\n",
       "      <td>95</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>43338</td>\n",
       "      <td>16:54:00</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>43338</td>\n",
       "      <td>16:59:00</td>\n",
       "      <td>3</td>\n",
       "      <td>325</td>\n",
       "      <td>23.8</td>\n",
       "      <td>51</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      日期        时间  击败   伤害  生存时间  治疗量  救援  积分\n",
       "0  43338  15:48:00   2  200  11.6   90   0  10\n",
       "1  43338  16:02:00   2  245  12.2  163   0   9\n",
       "2  43338  16:18:00   1  100  18.8   95   0  21\n",
       "3  43338  16:54:00   0   28   1.4    0   0 -31\n",
       "4  43338  16:59:00   3  325  23.8   51   0  21"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import tensorflow.compat.v1 as tf\n",
    "tf.disable_v2_behavior()\n",
    "# 这个会直接默认读取到这个Excel的第一个表单\n",
    "df=pd.read_excel('data.xlsx')\n",
    "# 默认读取所有数据，如果要读取前5行，可用df.head()\n",
    "data=df\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\lujia\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: FutureWarning: Method .as_matrix will be removed in a future version. Use .values instead.\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([[2, 200, 11.6, 90, 0, 10],\n",
       "       [2, 245, 12.2, 163, 0, 9],\n",
       "       [1, 100, 18.8, 95, 0, 21],\n",
       "       [0, 28, 1.4, 0, 0, -31],\n",
       "       [3, 325, 23.8, 51, 0, 21],\n",
       "       [0, 0, 2.5, 0, 0, -22],\n",
       "       [7, 582, 28.5, 375, 0, 32],\n",
       "       [1, 78, 20.1, 130, 0, 18],\n",
       "       [1, 100, 26.1, 0, 0, 19],\n",
       "       [1, 100, 20.8, 48, 0, 17],\n",
       "       [1, 121, 16.7, 28, 0, 17],\n",
       "       [1, 20, 25.2, 218, 0, 27],\n",
       "       [0, 0, 6.4, 39, 0, -15],\n",
       "       [2, 200, 19.7, 34, 0, 20],\n",
       "       [1, 100, 9.5, 3, 0, -2],\n",
       "       [1, 100, 7.1, 0, 0, 1],\n",
       "       [3, 300, 27.6, 219, 0, 32],\n",
       "       [2, 216, 26.0, 0, 0, 23],\n",
       "       [1, 100, 15.4, 30, 0, 10],\n",
       "       [0, 0, 11.1, 0, 0, -1],\n",
       "       [1, 70, 12.5, 0, 0, 10],\n",
       "       [3, 250, 28.8, 192, 0, 26],\n",
       "       [2, 254, 23.3, 25, 0, 15],\n",
       "       [1, 121, 15.7, 13, 0, 11],\n",
       "       [0, 0, 10.4, 0, 0, 7],\n",
       "       [1, 99, 13.8, 7, 0, 7],\n",
       "       [1, 151, 17.6, 0, 0, 12]], dtype=object)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 转矩阵\n",
    "matData = data.as_matrix()\n",
    "# 截取 【击败数】 到 【积分】 列\n",
    "train_data=matData[:,2:] \n",
    "train_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取行数\n",
    "count = int(matData.size / matData[0].size)\n",
    "count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 定义训练数据\n",
    "x_data = train_data[:, 0:4]\n",
    "y_data = train_data[:, 5:6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 取训练数据进行归一化处理\n",
    "max_val = 0\n",
    "min_val = 0\n",
    "# x 数据集处理\n",
    "for i in range(4):\n",
    "    t_min = x_data[:,i].min()\n",
    "    t_max = x_data[:,i].max()\n",
    "    x_data[:,i] = (x_data[:,i] - t_min)/(t_max - t_min)\n",
    "# y 数据集处理\n",
    "max_val = y_data[:,0].max()\n",
    "min_val = y_data[:,0].min()\n",
    "y_data[:,0] = (y_data[:,0] - min_val)/ (max_val-min_val)\n",
    "# 反归一化 y_data[:,0] = y_data[:,0] * (max_val-min_val) + min_val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "def add_layer(inputs, in_size, out_size, activation_function=None):\n",
    "    # add one more layer and return the output of this layer\n",
    "    Weights = tf.Variable(tf.random_normal([in_size, out_size]))\n",
    "    biases = tf.Variable(tf.zeros([1, out_size]))\n",
    "    Wx_plus_b = tf.matmul(inputs, Weights) + biases\n",
    "    if activation_function is None:\n",
    "        outputs = Wx_plus_b\n",
    "    else:\n",
    "        outputs = activation_function(Wx_plus_b)\n",
    "    return outputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0018213531"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 定义模型\n",
    "xs = tf.placeholder(tf.float32, [None, 4])\n",
    "ys = tf.placeholder(tf.float32, [None, 1])\n",
    "node_num = 5\n",
    "learn_rate = 0.1\n",
    "# add hidden layer1\n",
    "layer1 = add_layer(xs, 4, node_num, activation_function=tf.tanh)\n",
    "# add hidden layer2\n",
    "layer2 = add_layer(layer1, node_num, node_num, activation_function=tf.tanh)\n",
    "# add hidden layer3\n",
    "layer3 = add_layer(layer2, node_num, node_num, activation_function=tf.tanh)\n",
    "# add output layer\n",
    "prediction = add_layer(layer3, node_num, 1)\n",
    "loss = tf.reduce_mean(tf.square(ys - prediction))\n",
    "# 创建优化器\n",
    "train_step = tf.train.GradientDescentOptimizer(learn_rate).minimize(loss)\n",
    "\n",
    "# 定义初始化变量操作\n",
    "init = tf.global_variables_initializer()\n",
    "sess = tf.Session()\n",
    "\n",
    "# 启动会话\n",
    "sess.run(init)\n",
    "# 开始训练\n",
    "train_epoch = 5000\n",
    "# 记录训练步数\n",
    "step = 0\n",
    "# 记录误差\n",
    "last_loss = 0\n",
    "for epoch in range(train_epoch):\n",
    "    # training\n",
    "    _,loss_value=sess.run([train_step,loss], feed_dict={xs: x_data, ys: y_data})\n",
    "    last_loss = loss_value\n",
    "last_loss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0.66019803],\n",
       "       [0.6280735 ],\n",
       "       [0.81205434],\n",
       "       [0.00987861],\n",
       "       [0.7997965 ],\n",
       "       [0.13231596],\n",
       "       [1.0027418 ],\n",
       "       [0.84246707],\n",
       "       [0.8088068 ],\n",
       "       [0.81135076],\n",
       "       [0.72198564],\n",
       "       [0.8792031 ],\n",
       "       [0.2894834 ],\n",
       "       [0.79120296],\n",
       "       [0.46002665],\n",
       "       [0.4972715 ],\n",
       "       [0.9118236 ],\n",
       "       [0.8355796 ],\n",
       "       [0.72108847],\n",
       "       [0.5182859 ],\n",
       "       [0.63472563],\n",
       "       [0.93294114],\n",
       "       [0.78071386],\n",
       "       [0.67821497],\n",
       "       [0.47703713],\n",
       "       [0.6407996 ],\n",
       "       [0.67336196]], dtype=float32)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prediction_value = sess.run(prediction, feed_dict={xs: x_data})\n",
    "prediction_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 10.592476  ],\n",
       "       [  8.56863   ],\n",
       "       [ 20.159424  ],\n",
       "       [-30.377647  ],\n",
       "       [ 19.38718   ],\n",
       "       [-22.664093  ],\n",
       "       [ 32.172733  ],\n",
       "       [ 22.075424  ],\n",
       "       [ 19.954826  ],\n",
       "       [ 20.115097  ],\n",
       "       [ 14.485096  ],\n",
       "       [ 24.389793  ],\n",
       "       [-12.762547  ],\n",
       "       [ 18.845787  ],\n",
       "       [ -2.01832   ],\n",
       "       [  0.32810593],\n",
       "       [ 26.444885  ],\n",
       "       [ 21.641514  ],\n",
       "       [ 14.428574  ],\n",
       "       [  1.652008  ],\n",
       "       [  8.987713  ],\n",
       "       [ 27.775291  ],\n",
       "       [ 18.184975  ],\n",
       "       [ 11.727543  ],\n",
       "       [ -0.946661  ],\n",
       "       [  9.370373  ],\n",
       "       [ 11.4218025 ]], dtype=float32)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 反归一化 y_data[:,0] = y_data[:,0] * (max_val-min_val) + min_val\n",
    "prediction_value = prediction_value * (max_val-min_val) + min_val\n",
    "prediction_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
